One requirement of this method is that you install the PEAR::Spreadsheet_Excel_Writer module on your webserver. If you don't have permission to do that, contact your systems administrator and ask him to to it. It's a two-second job. All you need to do is connect as root and issue the following command:
root@server: pear install Spreadsheet_Excel_Writer
This module was intended as a clone of the Spreadsheet::WriteExcel Perl module (on CPAN). It allows you to create an Excel spreadsheet without the use of COM object. However, because the module uses the BIFF5 format, it currently only supports Excel version 5, so features in newer version of excel aren't available at this stage.
Now you are ready to start coding. In this example I start off by pulling in some libraries which manage my database connection.. they just define the $db_name, $db_user, $db_password, $db_hostname and all that stuff, and then provide a call to mysql_select_db() ready for my connection. If you don't know how to do this stuff, then read up on that first - or if you don't want to that's fine. Afterall, this article is about generating spreadsheets,
So the first and foremost thing that has to happen in your php is a call to require_once. This enables us to pull in the Writer.php class definition, which now resides somewhere in your php path. (probably under /usr/local/lib/php/Spreadsheet/Excel/).
require_once 'Spreadsheet/Excel/Writer.php';
This makes all the classes and methods available to the rest of our script, so the next job is to call the constructor on this writer class and assign the newly created workbook object to a variable which we will call $workbook:
$workbook = new Spreadsheet_Excel_Writer();
From here we can really do whatever we like with the workbook. We can add worksheets, define formatting directives, insert data and formulae.. really all of the things that you would want to do with a basic spreadsheet. In this example I'm going to use an addressbook table in my database, load all the information out of it and present it in a spreadsheet.
First I want to define the column headers and I want these to be in bold. For this we use the addFormat() method to say that we want to create a new set of formatting rules. This (in true OOP style) creates a format object, w:-o
hich we assign to $format_bold (that's what I'm going to call it), and then we call methods belonging to the format class to specify exactly what we want, for example setBold(), which says that the format being called will have bold text.
$workbook = new Spreadsheet_Excel_Writer();
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
You might also notice something else in here - and that's the use of the '&' character when creating the workbook. I have done this because an Excel Spreadsheet can be quite an unwieldly thing - I mean there's a lot of metadata in there even before you start inserting cell data. Using the '&' character tells the programme to pass me a reference to the object which has been created not the object itself. This is really just a pointer - it tells me the precise location in memory where the object is stored. The result of this is that I don't have duplicates of the spreadhseet object flying around the place and taking up valuable resources. Instead I'm just using a reference.
So lets get on with creating a worksheet and actually writing the column title data into the top row:
$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "name", $format_bold);
$worksheet->write(0, 1, "address", $format_bold);
$worksheet->write(0, 2, "phone", $format_bold);
$worksheet->write(0, 3, "email", $format_bold);
$worksheet->write(0, 4, "comments", $format_bold);
So once again I call a constructor method and pass back a reference to the newly created object. I can then manipulate the worksheed object with the methods on offer - well in this case I just use write(), specifying each time a row number, column number, some text and finally I pass in a format object (the one created earlier - this will make the top row bold).
Well that was simple enough. All that's required now is some code to put the data in under the column headings and we're just about there:
$query="select * from addresses";
$result=mysql_db_query($db_name,$query);
First I run a query and grab the returned result object back into $result. Now I just iterate through the result, grabbing row contents one at a time and incrementing my way down through the spreadsheet rows with the captured data.
$i=1;
while($row=mysql_fetch_assoc($result)){
$worksheet->write($i, 0, "$row[name]");
$worksheet->write($i, 1, "$row[address]");
$worksheet->write($i, 2, "$row[phone]");
$worksheet->write($i, 3, "$row[email]");
$worksheet->write($i, 4, "$row[comments]");
$i++;
}
So this time I don't specify a format and the text just goes in as regular weighted text. The only thing that's left for me to do now is to send the freshly generated spreadsheet up to the user. The Workbook class does come with a send() method which send the object up to the client's browser, bundling in all the correct MIME headers and wot not. The result is that the user (who has presumably just clicked on a link to call this php page) will almost instantly get an Excel screen loading before their eyes with all the address book data contained within.
You can then clean up with close() and end the script
$workbook->close();
?>
Please reply if you have any further comments, or click on the 'subscribe' icon at the botton of the PHP/MySQL channel to receive an email notification next time an article is posted.
All that's left for me to do now is to provide all the sources for this example and to wish you a good day.
<?php
/*
$Id: report_all.php,v 1.1 2004/08/30 16:03:40 chris Exp $
generate a spreadsheed from and addressbook in mysql database.
*/
include "dblib.php";
DBConnect();
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "name", $format_bold);
$worksheet->write(0, 1, "address", $format_bold);
$worksheet->write(0, 2, "phone", $format_bold);
$worksheet->write(0, 3, "email", $format_bold);
$worksheet->write(0, 4, "comments", $format_bold);
# start by opening a query string
$fullquery="select * from smslog";
$result=mysql_db_query($db_name,$fullquery);
$i=1;
while($row=mysql_fetch_assoc($result)){
$worksheet->write($i, 0, "$row[name]");
$worksheet->write($i, 1, "$row[address]");
$worksheet->write($i, 2, "$row[phone]");
$worksheet->write($i, 3, "$row[email]");
$worksheet->write($i, 4, "$row[comments]");
$i++;
}
$workbook->send('test.xls');
$workbook->close();
?>
follow me on twitter: http://twitter.com/planet_guru
